package com.is.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.is.dbconn.DBConnect;
import com.is.model.ParamReasonTypeGS;

public class ParamReasonTypeGSControl {
	
	
	public static boolean insertGS(List<ParamReasonTypeGS> gss) {
		DBConnect dbConnect = new DBConnect();
		String sql = "insert into param_reasontype_gs(reasonTypeId,num,stuyear) values(?,?,?)";
		PreparedStatement pstmt = null;
		dbConnect.setAutoCommit(false);
		    dbConnect.prepareStatement(sql);
		    pstmt = dbConnect.getPreparedStatement();
		    try {
				for(ParamReasonTypeGS gs :gss ){
					int index = 1;
					pstmt.setInt(index++,gs.getReasonTypeId());
					pstmt.setInt(index++,gs.getNum());
					pstmt.setInt(index++,gs.getStuyear());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				dbConnect.commit();
				dbConnect.setAutoCommit(true);
				return true;
			} catch (Exception e) {
				// TODO: handle exception
				dbConnect.rollback();
				e.printStackTrace();
				System.out.println("insertGS ERROR");
				return false;
			}finally{
				dbConnect.close();
			}
	}
	
	public  static int getNumByTypeAndStuyear(int type,int stuyear) {
		DBConnect dbConnect = new DBConnect();
		String sql = "select num from param_reasontype_gs where stuyear='"+stuyear+"' and reasonTypeId = '"+type+"'";
		int num = 0;
		ResultSet rs = null;
		dbConnect.prepareStatement(sql);
		try {
			rs =dbConnect.executeQuery();
			if(rs.next()){
				num = rs.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getNumByTypeAndStuyear ERROR");
		}finally{
			dbConnect.close();
		}
		return num;
	}
	
	public  static ParamReasonTypeGS getGSByTypeAndStuyear(int type,int stuyear) {
		DBConnect dbConnect = new DBConnect();
		String sql = "select * from param_reasontype_gs where stuyear='"+stuyear+"' and reasonTypeId = '"+type+"'";
		ResultSet rs = null;
		ParamReasonTypeGS gs= null;
		dbConnect.prepareStatement(sql);
		try {
			rs =dbConnect.executeQuery();
			if(rs.next()){
			        gs= new ParamReasonTypeGS();
				gs.setId(rs.getInt("id"));
				gs.setNum(rs.getInt("num"));
				gs.setReasonTypeId(rs.getInt("reasonTypeId"));
				gs.setStuyear(rs.getInt("stuyear"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getNumByTypeAndStuyear ERROR");
		}finally{
			dbConnect.close();
		}
		return gs;
	}
	
	public static boolean isInserted(int stuyear) {
		DBConnect dbConnect = new DBConnect();
		String sql = "select id from param_reasontype_gs where stuyear='"+stuyear+"' ";
		dbConnect.prepareStatement(sql);
		ResultSet rs = null;
		boolean flag = false;
		try {
			rs = dbConnect.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("isInserted ERROR");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
	
	public static List<ParamReasonTypeGS> getReasonTypeGSByStuyear(int stuyear) {
	    DBConnect dbConnect = new DBConnect();
	    List<ParamReasonTypeGS> gss = new ArrayList<ParamReasonTypeGS>();
	    String sql = "select * from param_reasontype_gs where stuyear='"+stuyear+"'";
	    ResultSet rSet = null;
	    dbConnect.prepareStatement(sql);
	    try {
		rSet = dbConnect.executeQuery();
		while(rSet.next()){
		    ParamReasonTypeGS gs = new ParamReasonTypeGS();
		    gs.setId(rSet.getInt("id"));
		    gs.setReasonTypeId(rSet.getInt("reasonTypeId"));
		    gs.setNum(rSet.getInt("num"));
		    gs.setStuyear(stuyear);
		    gss.add(gs);
		}
	    } catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("getReasonTypeGSByStuyear ERROR");
	    }finally{
		dbConnect.close();
	    }
	    return gss;
	}
	
	public static boolean updateGSByStuyear(List<ParamReasonTypeGS> gss) {
	    DBConnect dbConnect = new DBConnect();
            String sql = "update  param_reasontype_gs set num=? where id=?";
	    PreparedStatement pstmt = null;
	    dbConnect.setAutoCommit(false);
	    dbConnect.prepareStatement(sql);
	    pstmt = dbConnect.getPreparedStatement();
	    try {
			for(ParamReasonTypeGS gs :gss ){
				int index = 1;
				pstmt.setInt(index++,gs.getNum());
				pstmt.setInt(index++,gs.getId());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			dbConnect.rollback();
			e.printStackTrace();
			System.out.println("updateGSByStuyear(List) ERROR");
			return false;
		}finally{
			dbConnect.close();
		}
	}

}
